﻿use persons
go
------------------9----------------------------------------------------------------
/*Find all grandmothers*/
SELECT  
[Person].[FirstName], 
[Person].[LastName] 
FROM [dbo].[Person]
WHERE [Person].[ID] IN( SELECT DISTINCT  
[Person].[fk_motherId]
FROM [dbo].[Person] WHERE [Person].[ID] IN( SELECT DISTINCT 
[Person].[fk_motherId]
FROM [dbo].[Person] WHERE [Person].[fk_motherId] IS NOT NULL
UNION
SELECT DISTINCT 
[Person].[fk_fatherId]
FROM [dbo].[Person] WHERE [Person].[fk_fatherId] IS NOT NULL))

SELECT  
[Person].[FirstName], 
[Person].[LastName] 
FROM [persons].[dbo].[Person]
WHERE [Person].[ID] IN (SELECT DISTINCT  
[Person].[fk_motherId]
FROM [dbo].[Person] WHERE [Person].[ID] IN (SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [MOTHER] 
WHERE [Person].[ID]=[MOTHER].[fk_motherId] AND [Person].[fk_motherId] IS NOT NULL
UNION
SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person], [dbo].[Person] AS [FATHER] WHERE [Person].[ID]=[FATHER].[fk_fatherId] AND [Person].[fk_fatherId] IS NOT NULL))

SELECT  
[Person].[FirstName], 
[Person].[LastName] 
FROM [persons].[dbo].[Person]
WHERE [Person].[ID] IN(SELECT DISTINCT  
[Person].[fk_motherId]
FROM [persons].[dbo].[Person] WHERE  Person.ID IN (SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [MOTHER] ON [Person].[ID]=[MOTHER].[fk_motherId] WHERE [Person].[fk_motherId] IS NOT NULL
UNION
SELECT DISTINCT 
[Person].[ID]
FROM [dbo].[Person] INNER JOIN [dbo].[Person] AS [FATHER] ON [Person].[ID]=[FATHER].[fk_fatherId] WHERE [Person].[fk_fatherId] IS NOT NULL))
